﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Web;

namespace Com.JunCaiSoft.Common.AssistClass
{
    public class NPOIExtension
    {
        #region 从datatable中将数据导出到excel
        public static HSSFWorkbook ExportWorkbook(DataTable dtSource, string SheetName, string ColumnProperty)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            if (SheetName == null || SheetName == "") SheetName = "Sheet1";
            HSSFSheet sheet = workbook.CreateSheet(SheetName) as HSSFSheet;

            // 标题样式
            HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            headStyle.Alignment = HorizontalAlignment.Center;
            headStyle.VerticalAlignment = VerticalAlignment.Center;
            headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            headStyle.WrapText = true;
            HSSFFont font = workbook.CreateFont() as HSSFFont;
            font.IsBold = true;
            headStyle.SetFont(font);

            //内容样式
            HSSFCellStyle cellStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            cellStyle.CloneStyleFrom(headStyle);
            cellStyle.Alignment = HorizontalAlignment.Center;
            font = workbook.CreateFont() as HSSFFont;
            font.IsBold = true;
            cellStyle.SetFont(font);
            HSSFCellStyle cellStyleLeft = workbook.CreateCellStyle() as HSSFCellStyle;
            cellStyleLeft.CloneStyleFrom(cellStyle);
            cellStyleLeft.Alignment = HorizontalAlignment.Left;
            HSSFCellStyle cellStyleRight = workbook.CreateCellStyle() as HSSFCellStyle;
            cellStyleRight.CloneStyleFrom(cellStyle);
            cellStyleRight.Alignment = HorizontalAlignment.Right;

            int rowIndex = 0;
            #region 列头及样式
            HSSFRow headerRow = sheet.CreateRow(rowIndex) as HSSFRow;
            foreach (DataColumn column in dtSource.Columns)
            {
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
            }
            #endregion
            rowIndex++;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 填充内容
                HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
                foreach (DataColumn column in dtSource.Columns)
                {
                    HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
                    newCell.CellStyle = cellStyle;
                    AutoSetType(newCell, column, row);
                }

                #endregion
                rowIndex++;
            }
            AutoSizeColumns(sheet);

            if (ColumnProperty != null)
            {
                string[] ColumnArray = ColumnProperty.Split('|');
                foreach (string Column in ColumnArray)
                {
                    string[] ValueArray = Column.Split(',');
                    if (ValueArray.Length > 2)
                    {
                        int cols = int.Parse(ValueArray[1]);
                        switch (ValueArray[0].ToString())
                        {
                            case "Merge":
                                int precols = 0;
                                int.TryParse(ValueArray[2], out precols);
                                GroupCol(sheet, 1, cols, precols);
                                break;
                            case "Width":
                                int columnWidth = 0;
                                int.TryParse(ValueArray[2], out columnWidth);
                                sheet.SetColumnWidth(cols, columnWidth * 256);
                                break;
                            case "Align":
                                string Value = ValueArray[2].ToString();
                                for (int j = 1; j <= sheet.LastRowNum; j++)
                                {
                                    ICell currentCell = sheet.GetRow(j).GetCell(cols);
                                    switch (Value)
                                    {
                                        case "center":
                                            currentCell.CellStyle = cellStyle;
                                            break;
                                        case "left":
                                            currentCell.CellStyle = cellStyleLeft;
                                            break;
                                        case "right":
                                            currentCell.CellStyle = cellStyleRight;
                                            break;
                                    }
                                }
                                break;
                            case "DataType":
                                string Value2 = ValueArray[2].ToString();
                                for (int j = 1; j <= sheet.LastRowNum; j++)
                                {
                                    ICell currentCell = sheet.GetRow(j).GetCell(cols);
                                    string drValue = currentCell.ToString();
                                    switch (Value2)
                                    {
                                        case "double":
                                            double result;
                                            if (double.TryParse(drValue, out result))
                                                currentCell.SetCellValue(result);
                                            break;
                                        case "string":
                                            currentCell.SetCellValue(drValue.ToString());
                                            break;
                                    }
                                }
                                break;
                        }
                    }
                }
            }
            return workbook;
        }

        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        public static MemoryStream ExportDT(DataTable dtSource, string SheetName, string ColumnProperty)
        {
            using (MemoryStream ms = new MemoryStream())
            {
                HSSFWorkbook workbook = ExportWorkbook(dtSource, SheetName, ColumnProperty);
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }

        public static void ExportDTtoExcel(DataTable dtSource, string strFileName, bool SaveFile)
        {
            ExportDTtoExcel(dtSource, strFileName, SaveFile, null, null);
        }

        /// <summary>
        /// DataTable导出到Excel文件
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="sFileName">文件名</param>
        /// <param name="SaveFile">是否需保存</param>
        /// <param name="SheetName">Sheet名</param>
        /// <param name="ColumnProperty">列属性集,多个属性集以"|"分隔,值以","分隔,范例:"Merge,0,0|Align,2,right|Width,4,100|DataType,5,double"</param>
        public static void ExportDTtoExcel(DataTable dtSource, string sFileName, bool SaveFile, string SheetName, string ColumnProperty)
        {
            using (MemoryStream ms = ExportDT(dtSource, SheetName, ColumnProperty))
            {
                if (SaveFile)
                {
                    using (FileStream fs = new FileStream(sFileName, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                    }
                    System.IO.FileInfo fileInfo = new System.IO.FileInfo(sFileName);
                    sFileName = fileInfo.Name;
                }
                //System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename=" + HttpUtility.UrlEncode(sFileName)));
                //System.Web.HttpContext.Current.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
                //System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
                //System.Web.HttpContext.Current.Response.Flush();
                ms.Close();
                ms.Dispose();
                //System.Web.HttpContext.Current.Response.End();
            }
        }

        /// <summary>
        /// 导出EXCEL,可以导出多个sheet(不设置列宽)
        /// </summary>
        /// <param name="dtSources">原始数据表</param>
        /// <param name="strFileName">文件名</param>
        /// <param name="SaveFile">是否需要保存此文件</param>
        /// <param name="NeedMargeColumns">需要合并的列，如：3,5</param>
        public static void ExportDTtoExcelNoWidth(DataTable[] dtSources, string strFileName, bool SaveFile, string NeedMargeColumns)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            if (NeedMargeColumns.Length > 0)
            {
                NeedMargeColumns = "," + NeedMargeColumns + ",";
            }
            HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            for (int k = 0; k < dtSources.Length; k++)
            {
                int[] margeColIndex = new int[dtSources[k].Columns.Count];//保存合并列的起始行
                string[] margeColValue = new string[dtSources[k].Columns.Count];//保存合并列对应的上一个值      
                HSSFSheet sheet = workbook.CreateSheet(dtSources[k].TableName.ToString()) as HSSFSheet;
                //填充表头
                HSSFRow dataRow = sheet.CreateRow(0) as HSSFRow;
                ICellStyle Headstyle = workbook.CreateCellStyle();
                //设置单元格的样式：水平对齐居中
                Headstyle.Alignment = HorizontalAlignment.Center;
                //新建一个字体样式对象
                IFont font = workbook.CreateFont();
                //设置字体加粗样式
                font.Boldweight = short.MaxValue;
                //使用SetFont方法将字体样式添加到单元格样式中 
                Headstyle.SetFont(font);
                foreach (DataColumn column in dtSources[k].Columns)
                {
                    margeColIndex[column.Ordinal] = 1;
                    margeColValue[column.Ordinal] = "";
                    ICell cell = dataRow.CreateCell(column.Ordinal);//创建单元格
                    cell.SetCellValue(column.ColumnName);//设置单元格的值
                    dataRow.GetCell(column.Ordinal).CellStyle = Headstyle; //将新的样式赋给单元格
                }
                //填充内容
                for (int i = 0; i < dtSources[k].Rows.Count; i++)
                {
                    dataRow = sheet.CreateRow(i + 1) as HSSFRow;
                    for (int j = 0; j < dtSources[k].Columns.Count; j++)
                    {
                        ICell newCell = dataRow.CreateCell(j);
                        bool canSetValue = false;
                        if (NeedMargeColumns.Length > 0 && (NeedMargeColumns.IndexOf("," + j.ToString() + ",") != -1)) //有需要合并的列,并且当前列需要合并              
                        {
                            if (margeColValue[j] == "")
                            {
                                canSetValue = true;
                                margeColIndex[j] = i + 1;
                                margeColValue[j] = dtSources[k].Rows[i][j].ToString();
                            }
                            else if (margeColValue[j] != dtSources[k].Rows[i][j].ToString())//不同的时候需要更新原来储存的值，并重新设置合并
                            {
                                canSetValue = true;
                                //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
                                sheet.AddMergedRegion(new CellRangeAddress(margeColIndex[j], i, j, j));
                                margeColIndex[j] = i + 1;
                                margeColValue[j] = dtSources[k].Rows[i][j].ToString();
                            }
                        }
                        else
                        {
                            canSetValue = true;
                        }
                        if (canSetValue)
                        {
                            //需要合并的列，1.第一行/最后一行,2.上一行值不同，下一行值相同，则给当前单元格赋值
                            switch (dtSources[k].Columns[j].DataType.ToString())
                            {
                                case "System.String"://字符串类型   
                                    newCell.SetCellValue(dtSources[k].Rows[i][j].ToString());
                                    break;
                                case "System.DateTime"://日期类型   
                                    DateTime dateV;
                                    DateTime.TryParse(dtSources[k].Rows[i][j].ToString(), out dateV);
                                    newCell.SetCellValue(dateV);
                                    newCell.CellStyle = dateStyle;//格式化显示   
                                    break;
                                case "System.Boolean"://布尔型   
                                    bool boolV = false;
                                    bool.TryParse(dtSources[k].Rows[i][j].ToString(), out boolV);
                                    newCell.SetCellValue(boolV);
                                    break;
                                case "System.Int16"://整型   
                                case "System.Int32":
                                case "System.Int64":
                                case "System.Byte":
                                    int intV = 0;
                                    int.TryParse(dtSources[k].Rows[i][j].ToString(), out intV);
                                    newCell.SetCellValue(intV);
                                    break;
                                case "System.Decimal"://浮点型   
                                case "System.Double":
                                    double doubV = 0;
                                    double.TryParse(dtSources[k].Rows[i][j].ToString(), out doubV);
                                    newCell.SetCellValue(doubV);
                                    break;
                                case "System.DBNull"://空值处理   
                                    newCell.SetCellValue("");
                                    break;
                                default:
                                    newCell.SetCellValue("");
                                    break;
                            }
                        }
                    }
                }
            }
            //保存
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                if (SaveFile)
                {
                    using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                    }
                    System.IO.FileInfo fileInfo = new System.IO.FileInfo(strFileName);
                    strFileName = fileInfo.Name;
                }
                else
                {
                    //System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename=" + HttpUtility.UrlEncode(strFileName)));
                    //System.Web.HttpContext.Current.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
                    //System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
                    //System.Web.HttpContext.Current.Response.Flush();
                    ms.Close();
                    ms.Dispose();
                    //System.Web.HttpContext.Current.Response.End();
                }
            }
        }
        /// <summary>
        /// 导出EXCEL,可以导出多个sheet
        /// </summary>
        /// <param name="dtSources">原始数据表</param>
        /// <param name="strFileName">文件名</param>
        /// <param name="SaveFile">是否需要保存此文件</param>
        /// <param name="NeedMargeColumns">需要合并的列，如：3,5（表示第三列和第五列各自合并单元格）</param>
        public static void ExportDTtoExcel(DataTable[] dtSources, string strFileName, bool SaveFile, string NeedMargeColumns)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            if (NeedMargeColumns.Length > 0)
            {
                NeedMargeColumns = "," + NeedMargeColumns + ",";
            }
            HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            for (int k = 0; k < dtSources.Length; k++)
            {
                int[] margeColIndex = new int[dtSources[k].Columns.Count];//保存合并列的起始行
                string[] margeColValue = new string[dtSources[k].Columns.Count];//保存合并列对应的上一个值      
                HSSFSheet sheet = workbook.CreateSheet(dtSources[k].TableName.ToString()) as HSSFSheet;
                //填充表头
                HSSFRow dataRow = sheet.CreateRow(0) as HSSFRow;
                ICellStyle Headstyle = workbook.CreateCellStyle();
                //设置单元格的样式：水平对齐居中
                Headstyle.Alignment = HorizontalAlignment.Center;
                //新建一个字体样式对象
                IFont font = workbook.CreateFont();
                //设置字体加粗样式
                font.Boldweight = short.MaxValue;
                //使用SetFont方法将字体样式添加到单元格样式中 
                Headstyle.SetFont(font);

                ICellStyle Rowstyle = workbook.CreateCellStyle();
                //设置单元格的样式：垂直对齐居中
                Rowstyle.VerticalAlignment = VerticalAlignment.Justify;

                //取得列宽
                int[] arrColWidth = new int[dtSources[k].Columns.Count];
                foreach (DataColumn item in dtSources[k].Columns)
                {
                    arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
                    margeColIndex[item.Ordinal] = 1;
                    margeColValue[item.Ordinal] = "";
                }
                for (int i = 0; i < dtSources[k].Rows.Count; i++)
                {
                    for (int j = 0; j < dtSources[k].Columns.Count; j++)
                    {
                        int intTemp = Encoding.GetEncoding(936).GetBytes(dtSources[k].Rows[i][j].ToString()).Length;
                        if (intTemp > arrColWidth[j])
                        {
                            arrColWidth[j] = intTemp;
                        }
                    }
                }
                foreach (DataColumn column in dtSources[k].Columns)
                {
                    ICell cell = dataRow.CreateCell(column.Ordinal);//创建单元格
                    cell.SetCellValue(column.ColumnName);//设置单元格的值
                    dataRow.GetCell(column.Ordinal).CellStyle = Headstyle; //将新的样式赋给单元格
                                                                           //设置列宽
                                                                           //sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                    if (arrColWidth[column.Ordinal] > 255)
                    {
                        arrColWidth[column.Ordinal] = 254;
                    }
                    else
                    {
                        sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                    }
                }
                //填充内容
                for (int i = 0; i < dtSources[k].Rows.Count; i++)
                {
                    dataRow = sheet.CreateRow(i + 1) as HSSFRow;
                    for (int j = 0; j < dtSources[k].Columns.Count; j++)
                    {
                        ICell newCell = dataRow.CreateCell(j);
                        bool canSetValue = false;
                        if (NeedMargeColumns.Length > 0 && (NeedMargeColumns.IndexOf("," + j.ToString() + ",") != -1)) //有需要合并的列,并且当前列需要合并              
                        {
                            if (margeColValue[j] == "")
                            {
                                canSetValue = true;
                                margeColIndex[j] = i + 1;
                                margeColValue[j] = dtSources[k].Rows[i][j].ToString();
                            }
                            else if (margeColValue[j] != dtSources[k].Rows[i][j].ToString())//不同的时候需要更新原来储存的值，并重新设置合并
                            {
                                canSetValue = true;
                                //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
                                sheet.AddMergedRegion(new CellRangeAddress(margeColIndex[j], i, j, j));
                                margeColIndex[j] = i + 1;
                                margeColValue[j] = dtSources[k].Rows[i][j].ToString();
                                newCell.CellStyle = Rowstyle;
                            }
                            else if (i == dtSources[k].Rows.Count - 1 && i != 0)//最后一行强制合并
                            {
                                //值相同本来是不用做任何事，但是最后一行，则需要合并
                                sheet.AddMergedRegion(new CellRangeAddress(margeColIndex[j], i + 1, j, j));
                                newCell.CellStyle = Rowstyle;
                            }
                        }
                        else
                        {
                            canSetValue = true;
                        }
                        if (canSetValue)
                        {
                            //需要合并的列，1.第一行/最后一行,2.上一行值不同，下一行值相同，则给当前单元格赋值
                            switch (dtSources[k].Columns[j].DataType.ToString())
                            {
                                case "System.String"://字符串类型   
                                    newCell.SetCellValue(dtSources[k].Rows[i][j].ToString());
                                    break;
                                case "System.DateTime"://日期类型   
                                    DateTime dateV;
                                    DateTime.TryParse(dtSources[k].Rows[i][j].ToString(), out dateV);
                                    newCell.SetCellValue(dateV);
                                    newCell.CellStyle = dateStyle;//格式化显示   
                                    break;
                                case "System.Boolean"://布尔型   
                                    bool boolV = false;
                                    bool.TryParse(dtSources[k].Rows[i][j].ToString(), out boolV);
                                    newCell.SetCellValue(boolV);
                                    break;
                                case "System.Int16"://整型   
                                case "System.Int32":
                                case "System.Int64":
                                case "System.Byte":
                                    int intV = 0;
                                    int.TryParse(dtSources[k].Rows[i][j].ToString(), out intV);
                                    newCell.SetCellValue(intV);
                                    break;
                                case "System.Decimal"://浮点型   
                                case "System.Double":
                                    double doubV = 0;
                                    double.TryParse(dtSources[k].Rows[i][j].ToString(), out doubV);
                                    newCell.SetCellValue(doubV);
                                    break;
                                case "System.DBNull"://空值处理   
                                    newCell.SetCellValue("");
                                    break;
                                default:
                                    newCell.SetCellValue("");
                                    break;
                            }
                        }
                    }
                }
            }
            //保存
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                if (SaveFile)
                {
                    using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                    }
                    System.IO.FileInfo fileInfo = new System.IO.FileInfo(strFileName);
                    strFileName = fileInfo.Name;
                }
                //System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename=" + HttpUtility.UrlEncode(strFileName)));
                //System.Web.HttpContext.Current.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
                //System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
                //System.Web.HttpContext.Current.Response.Flush();
                ms.Close();
                ms.Dispose();
                //System.Web.HttpContext.Current.Response.End();
            }
        }
        /// <summary>
        /// 导出EXCEL,可以导出多个sheet
        /// </summary>
        /// <param name="dtSources">原始数据表</param>
        /// <param name="strFileName">文件名</param>
        /// <param name="SaveFile">是否需要保存此文件</param>
        /// <param name="NeedMargeColumns">需要合并的列，如：3,5（表示第三列和第五列各自合并单元格）或者3,3:5（表示第三列合并，然后第五列合并时参考第三列再合并）</param>
        public static void ExportDTtoExcelExt(DataTable[] dtSources, string strFileName, bool SaveFile, string NeedMargeColumns)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            if (NeedMargeColumns.Length > 0)
            {
                NeedMargeColumns = "," + NeedMargeColumns + ",";
            }
            HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            for (int k = 0; k < dtSources.Length; k++)
            {
                int[] margeColIndex = new int[dtSources[k].Columns.Count];//保存合并列的起始行
                string[] margeColValue = new string[dtSources[k].Columns.Count];//保存合并列对应的上一个值      
                HSSFSheet sheet = workbook.CreateSheet(dtSources[k].TableName.ToString()) as HSSFSheet;
                //填充表头
                HSSFRow dataRow = sheet.CreateRow(0) as HSSFRow;
                ICellStyle Headstyle = workbook.CreateCellStyle();
                //设置单元格的样式：水平对齐居中
                Headstyle.Alignment = HorizontalAlignment.Center;
                //新建一个字体样式对象
                IFont font = workbook.CreateFont();
                //设置字体加粗样式
                font.Boldweight = short.MaxValue;
                //使用SetFont方法将字体样式添加到单元格样式中 
                Headstyle.SetFont(font);

                ICellStyle Rowstyle = workbook.CreateCellStyle();
                //设置单元格的样式：垂直对齐居中
                Rowstyle.VerticalAlignment = VerticalAlignment.Justify;

                //取得列宽
                int[] arrColWidth = new int[dtSources[k].Columns.Count];
                foreach (DataColumn item in dtSources[k].Columns)
                {
                    arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
                    margeColIndex[item.Ordinal] = 1;
                    margeColValue[item.Ordinal] = "";
                }
                for (int i = 0; i < dtSources[k].Rows.Count; i++)
                {
                    for (int j = 0; j < dtSources[k].Columns.Count; j++)
                    {
                        int intTemp = Encoding.GetEncoding(936).GetBytes(dtSources[k].Rows[i][j].ToString()).Length;
                        if (intTemp > arrColWidth[j])
                        {
                            arrColWidth[j] = intTemp;
                        }
                    }
                }
                foreach (DataColumn column in dtSources[k].Columns)
                {
                    ICell cell = dataRow.CreateCell(column.Ordinal);//创建单元格
                    cell.SetCellValue(column.ColumnName);//设置单元格的值
                    dataRow.GetCell(column.Ordinal).CellStyle = Headstyle; //将新的样式赋给单元格
                                                                           //设置列宽
                                                                           //sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);

                    if (arrColWidth[column.Ordinal] >= 255)
                    {
                        arrColWidth[column.Ordinal] = 254;
                    }
                    else
                    {
                        sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                    }
                }
                //填充内容
                for (int i = 0; i < dtSources[k].Rows.Count; i++)
                {
                    dataRow = sheet.CreateRow(i + 1) as HSSFRow;
                    for (int j = 0; j < dtSources[k].Columns.Count; j++)
                    {
                        ICell newCell = dataRow.CreateCell(j);
                        bool canSetValue = false;
                        if (NeedMargeColumns.Length > 0 && (NeedMargeColumns.IndexOf("," + j.ToString() + ",") != -1 || NeedMargeColumns.IndexOf(":" + j.ToString() + ",") != -1)) //有需要合并的列,并且当前列需要合并              
                        {
                            if (margeColValue[j] == "")
                            {
                                canSetValue = true;
                                margeColIndex[j] = i + 1;
                                margeColValue[j] = dtSources[k].Rows[i][j].ToString();
                            }
                            else if (NeedMargeColumns.IndexOf("," + j.ToString() + ",") != -1)//单独列合并
                            {
                                if (margeColValue[j] != dtSources[k].Rows[i][j].ToString())//不同的时候需要更新原来储存的值，并重新设置合并
                                {
                                    canSetValue = true;
                                    //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
                                    sheet.AddMergedRegion(new CellRangeAddress(margeColIndex[j], i, j, j));
                                    margeColIndex[j] = i + 1;
                                    margeColValue[j] = dtSources[k].Rows[i][j].ToString();
                                    newCell.CellStyle = Rowstyle;
                                }
                                else if (i == dtSources[k].Rows.Count - 1 && i != 0)//最后一行强制合并
                                {
                                    //值相同本来是不用做任何事，但是最后一行，则需要合并
                                    sheet.AddMergedRegion(new CellRangeAddress(margeColIndex[j], i + 1, j, j));
                                    newCell.CellStyle = Rowstyle;
                                }
                            }
                            else//参考前列合并
                            {
                                //获取前列
                                int refercol = 0;
                                string[] MargeCols = NeedMargeColumns.Split(',');
                                for (int dd = 0; dd < MargeCols.Length; dd++)
                                {
                                    if (MargeCols[dd].Length > 0 && ("," + MargeCols[dd] + ",").IndexOf(":" + j.ToString() + ",") != -1)
                                    {
                                        refercol = Convert.ToInt32(MargeCols[dd].Substring(0, MargeCols[dd].IndexOf(":")));
                                        break;
                                    }
                                }
                                if (dtSources[k].Rows[i - 1][refercol].ToString() != dtSources[k].Rows[i][refercol].ToString())
                                {
                                    canSetValue = true;
                                    //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
                                    sheet.AddMergedRegion(new CellRangeAddress(margeColIndex[j], i, j, j));
                                    margeColIndex[j] = i + 1;
                                    margeColValue[j] = dtSources[k].Rows[i][j].ToString();
                                    newCell.CellStyle = Rowstyle;
                                }
                                else if (margeColValue[j] != dtSources[k].Rows[i][j].ToString())//不同的时候需要更新原来储存的值，并重新设置合并
                                {
                                    canSetValue = true;
                                    //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
                                    sheet.AddMergedRegion(new CellRangeAddress(margeColIndex[j], i, j, j));
                                    margeColIndex[j] = i + 1;
                                    margeColValue[j] = dtSources[k].Rows[i][j].ToString();
                                    newCell.CellStyle = Rowstyle;
                                }
                                else if (i == dtSources[k].Rows.Count - 1 && i != 0)//最后一行强制合并
                                {
                                    //值相同本来是不用做任何事，但是最后一行，则需要合并
                                    sheet.AddMergedRegion(new CellRangeAddress(margeColIndex[j], i + 1, j, j));
                                    newCell.CellStyle = Rowstyle;
                                }
                            }
                        }
                        else
                        {
                            canSetValue = true;
                        }
                        if (canSetValue)
                        {
                            #region 需要合并的列，1.第一行/最后一行,2.上一行值不同，下一行值相同，则给当前单元格赋值
                            switch (dtSources[k].Columns[j].DataType.ToString())
                            {
                                case "System.String"://字符串类型   
                                    newCell.SetCellValue(dtSources[k].Rows[i][j].ToString());
                                    break;
                                case "System.DateTime"://日期类型   
                                    DateTime dateV;
                                    DateTime.TryParse(dtSources[k].Rows[i][j].ToString(), out dateV);
                                    newCell.SetCellValue(dateV);
                                    newCell.CellStyle = dateStyle;//格式化显示   
                                    break;
                                case "System.Boolean"://布尔型   
                                    bool boolV = false;
                                    bool.TryParse(dtSources[k].Rows[i][j].ToString(), out boolV);
                                    newCell.SetCellValue(boolV);
                                    break;
                                case "System.Int16"://整型   
                                case "System.Int32":
                                case "System.Int64":
                                case "System.Byte":
                                    int intV = 0;
                                    int.TryParse(dtSources[k].Rows[i][j].ToString(), out intV);
                                    newCell.SetCellValue(intV);
                                    break;
                                case "System.Decimal"://浮点型   
                                case "System.Double":
                                    double doubV = 0;
                                    double.TryParse(dtSources[k].Rows[i][j].ToString(), out doubV);
                                    newCell.SetCellValue(doubV);
                                    break;
                                case "System.DBNull"://空值处理   
                                    newCell.SetCellValue("");
                                    break;
                                default:
                                    newCell.SetCellValue("");
                                    break;
                            }
                            #endregion
                        }
                    }
                }
            }
            //保存
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                if (SaveFile)
                {
                    using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                    }
                    System.IO.FileInfo fileInfo = new System.IO.FileInfo(strFileName);
                    strFileName = fileInfo.Name;
                }
                //System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename=" + HttpUtility.UrlEncode(strFileName)));
                //System.Web.HttpContext.Current.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
                //System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
                //System.Web.HttpContext.Current.Response.Flush();
                ms.Close();
                ms.Dispose();
                //System.Web.HttpContext.Current.Response.End();
            }
        }
        #endregion

        #region 从excel中将数据导出到datatable
        /// <summary>读取excel
        /// 默认第一行为标头
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <returns></returns>
        public static DataTable ImportExceltoDt(string strFileName)
        {
            DataTable dt = new DataTable();
            IWorkbook wk = null;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                if (strFileName.EndsWith(".xlsx"))
                    wk = new XSSFWorkbook(file);
                else
                    wk = new HSSFWorkbook(file);
            }
            ISheet sheet = wk.GetSheetAt(0);
            dt = ImportDt(sheet, 0, true);
            return dt;
        }

        /// <summary>
        /// 读取excel
        /// </summary>
        /// <param name="strFileName">excel文件路径</param>
        /// <param name="sheet">需要导出的sheet</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <returns></returns>
        public static DataTable ImportExceltoDt(string strFileName, string SheetName, int HeaderRowIndex)
        {
            DataTable dt = new DataTable();
            IWorkbook wk = null;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                if (strFileName.EndsWith(".xlsx"))
                    wk = new XSSFWorkbook(file);
                else
                    wk = new HSSFWorkbook(file);
            }
            ISheet sheet = wk.GetSheet(SheetName) as ISheet;
            dt = ImportDt(sheet, HeaderRowIndex, true);
            //ExcelFileStream.Close();
            wk = null;
            sheet = null;
            return dt;
        }

        /// <summary>
        /// 读取excel
        /// </summary>
        /// <param name="strFileName">excel文件路径</param>
        /// <param name="sheet">需要导出的sheet序号</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <returns></returns>
        public static DataTable ImportExceltoDt(string strFileName, int SheetIndex, int HeaderRowIndex)
        {
            DataTable dt = new DataTable();
            IWorkbook wk = null;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                if (strFileName.EndsWith(".xlsx"))
                    wk = new XSSFWorkbook(file);
                else
                    wk = new HSSFWorkbook(file);
            }
            ISheet sheet = wk.GetSheetAt(SheetIndex) as ISheet;
            dt = ImportDt(sheet, HeaderRowIndex, true);
            //ExcelFileStream.Close();
            wk = null;
            sheet = null;
            return dt;
        }

        /// <summary>
        /// 读取excel
        /// </summary>
        /// <param name="strFileName">excel文件路径</param>
        /// <param name="sheet">需要导出的sheet</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <returns></returns>
        public static DataTable ImportExceltoDt(string strFileName, string SheetName, int HeaderRowIndex, bool needHeader)
        {
            DataTable dt = new DataTable();
            IWorkbook wk = null;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                if (strFileName.EndsWith(".xlsx"))
                    wk = new XSSFWorkbook(file);
                else
                    wk = new HSSFWorkbook(file);
            }
            ISheet sheet = wk.GetSheet(SheetName) as ISheet;
            dt = ImportDt(sheet, HeaderRowIndex, needHeader);
            wk = null;
            sheet = null;
            return dt;
        }

        /// <summary>
        /// 读取excel
        /// </summary>
        /// <param name="strFileName">excel文件路径</param>
        /// <param name="sheet">需要导出的sheet序号</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <returns></returns>
        public static DataTable ImportExceltoDt(string strFileName, int SheetIndex, int HeaderRowIndex, bool needHeader)
        {
            DataTable dt = new DataTable();
            IWorkbook wk = null;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                if (strFileName.EndsWith(".xlsx"))
                    wk = new XSSFWorkbook(file);
                else
                    wk = new HSSFWorkbook(file);
            }
            ISheet sheet = wk.GetSheetAt(SheetIndex) as ISheet;
            dt = ImportDt(sheet, HeaderRowIndex, needHeader);
            wk = null;
            sheet = null;
            return dt;
        }

        /// <summary>
        /// 将制定sheet中的数据导出到datatable中
        /// </summary>
        /// <param name="sheet">需要导出的sheet</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <returns></returns>
        static DataTable ImportDt(ISheet sheet, int HeaderRowIndex, bool needHeader)
        {
            DataTable table = new DataTable();
            IRow headerRow;
            int cellCount;
            try
            {
                if (HeaderRowIndex < 0 || !needHeader)
                {
                    headerRow = sheet.GetRow(0) as IRow;
                    cellCount = headerRow.LastCellNum;

                    for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                    {
                        DataColumn column = new DataColumn(Convert.ToString("F" + (i + 1).ToString()));
                        table.Columns.Add(column);
                    }
                }
                else
                {
                    headerRow = sheet.GetRow(HeaderRowIndex) as IRow;
                    cellCount = headerRow.LastCellNum;

                    for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                    {
                        if (headerRow.GetCell(i) == null)
                        {
                            if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
                            {
                                DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
                                table.Columns.Add(column);
                            }
                            else
                            {
                                DataColumn column = new DataColumn(Convert.ToString(i));
                                table.Columns.Add(column);
                            }

                        }
                        else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
                        {
                            DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
                            table.Columns.Add(column);
                        }
                        else
                        {
                            DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
                            table.Columns.Add(column);
                        }
                    }
                }
                int rowCount = sheet.LastRowNum;
                for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
                {
                    try
                    {
                        IRow row;
                        if (sheet.GetRow(i) == null)
                        {
                            row = sheet.CreateRow(i) as IRow;
                        }
                        else
                        {
                            row = sheet.GetRow(i) as IRow;
                        }

                        DataRow dataRow = table.NewRow();

                        for (int j = row.FirstCellNum; j <= cellCount; j++)
                        {
                            try
                            {
                                if (row.GetCell(j) != null)
                                {
                                    switch (row.GetCell(j).CellType)
                                    {
                                        case CellType.String:
                                            string str = row.GetCell(j).StringCellValue;
                                            if (str != null && str.Length > 0)
                                            {
                                                dataRow[j] = str.ToString();
                                            }
                                            else
                                            {
                                                dataRow[j] = null;
                                            }
                                            break;
                                        case CellType.Numeric:
                                            if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
                                            {
                                                dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
                                            }
                                            else
                                            {
                                                dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
                                            }
                                            break;
                                        case CellType.Boolean:
                                            dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                                            break;
                                        case CellType.Error:
                                            dataRow[j] = row.GetCell(j).ErrorCellValue;
                                            break;
                                        case CellType.Formula:
                                            switch (row.GetCell(j).CachedFormulaResultType)
                                            {
                                                case CellType.String:
                                                    string strFORMULA = row.GetCell(j).StringCellValue;
                                                    if (strFORMULA != null && strFORMULA.Length > 0)
                                                    {
                                                        dataRow[j] = strFORMULA.ToString();
                                                    }
                                                    else
                                                    {
                                                        dataRow[j] = null;
                                                    }
                                                    break;
                                                case CellType.Numeric:
                                                    dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
                                                    break;
                                                case CellType.Boolean:
                                                    dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                                                    break;
                                                case CellType.Error:
                                                    dataRow[j] = row.GetCell(j).ErrorCellValue;
                                                    break;
                                                default:
                                                    dataRow[j] = "";
                                                    break;
                                            }
                                            break;
                                        default:
                                            dataRow[j] = "";
                                            break;
                                    }
                                }
                            }
                            catch (Exception)
                            {
                                //wl.WriteLogs(exception.ToString());
                            }
                        }
                        table.Rows.Add(dataRow);
                    }
                    catch (Exception)
                    {
                        //wl.WriteLogs(exception.ToString());
                    }
                }
            }
            catch (Exception)
            {
                //wl.WriteLogs(exception.ToString());
            }
            return table;
        }
        #endregion

        #region 更新excel中的数据
        /// <summary>
        /// 更新Excel表格
        /// </summary>
        /// <param name="outputFile">需更新的excel表格路径</param>
        /// <param name="sheetname">sheet名</param>
        /// <param name="updateData">需更新的数据</param>
        /// <param name="coluid">需更新的列号</param>
        /// <param name="rowid">需更新的开始行号</param>
        public static void UpdateExcel(string outputFile, int sheetIndex, string[] updateData, int coluid, int rowid)
        {
            FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);

            HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
            ISheet sheet1 = hssfworkbook.GetSheetAt(sheetIndex);
            for (int i = 0; i < updateData.Length; i++)
            {
                try
                {
                    if (sheet1.GetRow(i + rowid) == null)
                    {
                        sheet1.CreateRow(i + rowid);
                    }
                    if (sheet1.GetRow(i + rowid).GetCell(coluid) == null)
                    {
                        sheet1.GetRow(i + rowid).CreateCell(coluid);
                    }

                    sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]);
                }
                catch (Exception ex)
                {
                    // wl.WriteLogs(ex.ToString());
                    throw;
                }
            }
            try
            {
                readfile.Close();
                FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write);
                hssfworkbook.Write(writefile);
                writefile.Close();
            }
            catch (Exception ex)
            {
                // wl.WriteLogs(ex.ToString());
            }

        }

        /// <summary>
        /// 更新Excel表格
        /// </summary>
        /// <param name="outputFile">需更新的excel表格路径</param>
        /// <param name="sheetname">sheet名</param>
        /// <param name="updateData">需更新的数据</param>
        /// <param name="coluids">需更新的列号</param>
        /// <param name="rowid">需更新的开始行号</param>
        public static void UpdateExcel(string outputFile, int sheetIndex, string[][] updateData, int[] coluids, int rowid)
        {
            FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);

            HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
            readfile.Close();
            ISheet sheet1 = hssfworkbook.GetSheetAt(sheetIndex);
            for (int j = 0; j < coluids.Length; j++)
            {
                for (int i = 0; i < updateData[j].Length; i++)
                {
                    try
                    {
                        if (sheet1.GetRow(i + rowid) == null)
                        {
                            sheet1.CreateRow(i + rowid);
                        }
                        if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null)
                        {
                            sheet1.GetRow(i + rowid).CreateCell(coluids[j]);
                        }
                        sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]);
                    }
                    catch (Exception ex)
                    {
                        // wl.WriteLogs(ex.ToString());
                    }
                }
            }
            try
            {
                FileStream writefile = new FileStream(outputFile, FileMode.Create);
                hssfworkbook.Write(writefile);
                writefile.Close();
            }
            catch (Exception ex)
            {
                //wl.WriteLogs(ex.ToString());
            }
        }

        /// <summary>
        /// 更新Excel表格
        /// </summary>
        /// <param name="outputFile">需更新的excel表格路径</param>
        /// <param name="sheetname">sheet名</param>
        /// <param name="updateData">需更新的数据</param>
        /// <param name="coluid">需更新的列号</param>
        /// <param name="rowid">需更新的开始行号</param>
        public static void UpdateExcel(string outputFile, int sheetIndex, double[] updateData, int coluid, int rowid)
        {
            FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);

            HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
            ISheet sheet1 = hssfworkbook.GetSheetAt(sheetIndex);
            for (int i = 0; i < updateData.Length; i++)
            {
                try
                {
                    if (sheet1.GetRow(i + rowid) == null)
                    {
                        sheet1.CreateRow(i + rowid);
                    }
                    if (sheet1.GetRow(i + rowid).GetCell(coluid) == null)
                    {
                        sheet1.GetRow(i + rowid).CreateCell(coluid);
                    }

                    sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]);
                }
                catch (Exception ex)
                {
                    //wl.WriteLogs(ex.ToString());
                    throw;
                }
            }
            try
            {
                readfile.Close();
                FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write);
                hssfworkbook.Write(writefile);
                writefile.Close();
            }
            catch (Exception ex)
            {
                //wl.WriteLogs(ex.ToString());
            }

        }

        /// <summary>
        /// 更新Excel表格
        /// </summary>
        /// <param name="outputFile">需更新的excel表格路径</param>
        /// <param name="sheetname">sheet名</param>
        /// <param name="updateData">需更新的数据</param>
        /// <param name="coluids">需更新的列号</param>
        /// <param name="rowid">需更新的开始行号</param>
        public static void UpdateExcel(string outputFile, int sheetIndex, double[][] updateData, int[] coluids, int rowid)
        {
            FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);

            HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
            readfile.Close();
            ISheet sheet1 = hssfworkbook.GetSheetAt(sheetIndex);
            for (int j = 0; j < coluids.Length; j++)
            {
                for (int i = 0; i < updateData[j].Length; i++)
                {
                    try
                    {
                        if (sheet1.GetRow(i + rowid) == null)
                        {
                            sheet1.CreateRow(i + rowid);
                        }
                        if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null)
                        {
                            sheet1.GetRow(i + rowid).CreateCell(coluids[j]);
                        }
                        sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]);
                    }
                    catch (Exception ex)
                    {
                        //wl.WriteLogs(ex.ToString());
                    }
                }
            }
            try
            {
                FileStream writefile = new FileStream(outputFile, FileMode.Create);
                hssfworkbook.Write(writefile);
                writefile.Close();
            }
            catch (Exception ex)
            {
                //wl.WriteLogs(ex.ToString());
            }
        }

        #endregion

        public static int GetSheetNumber(string outputFile)
        {
            int number = 0;
            try
            {
                IWorkbook wk = null;
                FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
                if (outputFile.EndsWith(".xlsx"))
                    wk = new XSSFWorkbook(readfile);
                else
                    wk = new HSSFWorkbook(readfile);
                number = wk.NumberOfSheets;
            }
            catch (Exception)
            {
                //wl.WriteLogs(exception.ToString());
            }
            return number;
        }

        public static ArrayList GetSheetName(string outputFile)
        {
            ArrayList arrayList = new ArrayList();
            try
            {
                IWorkbook wk = null;
                FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
                if (outputFile.EndsWith(".xlsx"))
                    wk = new XSSFWorkbook(readfile);
                else
                    wk = new HSSFWorkbook(readfile);

                for (int i = 0; i < wk.NumberOfSheets; i++)
                {
                    arrayList.Add(wk.GetSheetName(i));
                }

                //var newFile2 = @"newbook.core.docx";
                //using (var fs = new FileStream(newFile2, FileMode.Create, FileAccess.Write))
                //{
                //    XWPFDocument doc = new XWPFDocument();
                //    var p0 = doc.CreateParagraph();
                //    p0.Alignment = ParagraphAlignment.CENTER;
                //    XWPFRun r0 = p0.CreateRun();
                //    r0.FontFamily = "microsoft yahei";
                //    r0.FontSize = 18;
                //    r0.IsBold = true;
                //    r0.SetText("This is title");

                //    var p1 = doc.CreateParagraph();
                //    p1.Alignment = ParagraphAlignment.LEFT;
                //    p1.IndentationFirstLine = 500;
                //    XWPFRun r1 = p1.CreateRun();
                //    r1.FontFamily = "·ÂËÎ";
                //    r1.FontSize = 12;
                //    r1.IsBold = true;
                //    r1.SetText("This is content, content content content content content content content content content");

                //    doc.Write(fs);
                //}
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return arrayList;
        }

        public static bool isNumeric(String message, out double result)
        {
            Regex rex = new Regex(@"^[-]?\d+[.]?\d*$");
            result = -1;
            if (rex.IsMatch(message))
            {
                result = double.Parse(message);
                return true;
            }
            else
                return false;
        }

        /// <summary>
        /// 根据其他行的内容合并ISheet中某列相同信息的行（单元格）,2列相等
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="StartRow">合并的开始行</param>
        /// <param name="cols">合并列</param>
        /// <param name="precols">参考列</param>
        public static void GroupCol(ISheet sheet, int StartRow, int cols, int precols)
        {
            if (sheet.PhysicalNumberOfRows < StartRow + 1 || cols > sheet.GetRow(0).PhysicalNumberOfCells - 1)
            {
                return;
            }
            int RowNum = 0;
            ICell oldPreCell = sheet.GetRow(StartRow).GetCell(precols);
            ICell oldCell = sheet.GetRow(StartRow).GetCell(cols);
            int i = StartRow + 1;
            while (i < sheet.PhysicalNumberOfRows)
            {

                ICell PreCell = sheet.GetRow(i).GetCell(precols);
                ICell Cell = sheet.GetRow(i).GetCell(cols);
                if (oldCell.ToString() == Cell.ToString() && oldPreCell.ToString() == PreCell.ToString())
                {
                    //Cell.SetCellValue("");
                    RowNum++;
                }
                else
                {
                    if (RowNum > 0)
                    {
                        CellRangeAddress region = new CellRangeAddress(StartRow, StartRow + RowNum, cols, cols);
                        sheet.AddMergedRegion(region);
                        StartRow += RowNum;
                    }
                    oldPreCell = PreCell;
                    oldCell = Cell;
                    StartRow += 1;
                    RowNum = 0;
                }
                i++;
            }
            if (RowNum > 0 && i == sheet.PhysicalNumberOfRows)
            {
                CellRangeAddress region = new CellRangeAddress(StartRow, StartRow + RowNum, cols, cols);
                sheet.AddMergedRegion(region);
                StartRow += RowNum;
            }
        }

        /// <summary>
        /// 自动设置Excel列宽，行高
        /// </summary>
        /// <param name="sheet">Excel表</param>
        public static void AutoSizeColumns(ISheet sheet)
        {
            int RowNum = sheet.LastRowNum;
            int ColumnNum = sheet.GetRow(0).LastCellNum;
            int HeightValue = 0;
            for (int i = 0; i < ColumnNum; i++)
            {
                int columnWidth = sheet.GetColumnWidth(i) / 256;//获取当前列宽度  
                for (int j = 1; j <= RowNum; j++)//在这一列上循环行  
                {
                    IRow currentRow = sheet.GetRow(j);
                    ICell currentCell = currentRow.GetCell(i);
                    int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;//获取当前单元格的内容宽度  
                    if (columnWidth < length + 1)
                        columnWidth = length + 1;
                    if (columnWidth > 40)
                        columnWidth = 40;

                    if (length > HeightValue) HeightValue = length;
                    currentRow.HeightInPoints = 20 * (HeightValue / 60 + 1);//高度自适应
                }
                sheet.SetColumnWidth(i, columnWidth * 256);
            }
        }

        public static void AutoSetType(ICell newCell, DataColumn column, DataRow row)
        {
            string drValue = row[column].ToString();
            switch (column.DataType.ToString())
            {
                case "System.String"://字符串类型
                                     //double result;
                                     //if (double.TryParse(drValue, out result) && result.ToString().Length != 15)
                                     //    newCell.SetCellValue(result);
                                     //else
                    newCell.SetCellValue(drValue);
                    break;
                case "System.DateTime"://日期类型   
                    DateTime dateV;
                    DateTime.TryParse(drValue, out dateV);
                    newCell.SetCellValue(dateV);
                    break;
                case "System.Boolean"://布尔型   
                    bool boolV = false;
                    bool.TryParse(drValue, out boolV);
                    newCell.SetCellValue(boolV);
                    break;
                case "System.Int16"://整型   
                case "System.Int32":
                case "System.Int64":
                case "System.Byte":
                    int intV = 0;
                    int.TryParse(drValue, out intV);
                    if (intV != 0)
                        newCell.SetCellValue(intV);
                    break;
                case "System.Decimal"://浮点型   
                case "System.Double":
                    double doubV = 0;
                    double.TryParse(drValue, out doubV);
                    if (doubV != 0)
                        newCell.SetCellValue(doubV);
                    break;
                case "System.DBNull"://空值处理   
                    newCell.SetCellValue("");
                    break;
                default:
                    newCell.SetCellValue("");
                    break;
            }
        }



        /// <summary>
        /// DataTable导出到Excel文件 两行表头
        /// </summary>
        /// <param name="dt_Title">表头表格</param>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="sFileName">文件名</param>
        /// <param name="SaveFile">是否需保存</param>
        /// <param name="SheetName">Sheet名</param>
        /// <param name="ColumnProperty">列属性集,多个属性集以"|"分隔,值以","分隔,范例:"Merge,0,0|Align,2,right|Width,4,100|DataType,5,double"</param>

        public static void ExportDTtoExcel_2Title(DataTable dt_Title, DataTable dtSource, string sFileName, bool SaveFile, string SheetName, string ColumnProperty)
        {
            using (MemoryStream ms = ExportDT_2Title(dt_Title, dtSource, SheetName, ColumnProperty))
            {
                if (SaveFile)
                {
                    using (FileStream fs = new FileStream(sFileName, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                    }
                    System.IO.FileInfo fileInfo = new System.IO.FileInfo(sFileName);
                    sFileName = fileInfo.Name;
                }
                //System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename=" + HttpUtility.UrlEncode(sFileName)));
                //System.Web.HttpContext.Current.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
                //System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
                //System.Web.HttpContext.Current.Response.Flush();
                ms.Close();
                ms.Dispose();
                //System.Web.HttpContext.Current.Response.End();
            }
        }
        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        public static MemoryStream ExportDT_2Title(DataTable dt_Title, DataTable dtSource, string SheetName, string ColumnProperty)
        {
            using (MemoryStream ms = new MemoryStream())
            {
                HSSFWorkbook workbook = ExportWorkbook_2Title(dt_Title, dtSource, SheetName, ColumnProperty);
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dt_Title"></param>
        /// <param name="dtSource"></param>
        /// <param name="SheetName"></param>
        /// <param name="ColumnProperty"></param>
        /// <returns></returns>
        public static HSSFWorkbook ExportWorkbook_2Title(DataTable dt_Title, DataTable dtSource, string SheetName, string ColumnProperty)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            if (SheetName == null || SheetName == "") SheetName = "Sheet1";
            HSSFSheet sheet = workbook.CreateSheet(SheetName) as HSSFSheet;

            // 标题样式
            HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            headStyle.Alignment = HorizontalAlignment.Center;
            headStyle.VerticalAlignment = VerticalAlignment.Center;
            headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            headStyle.WrapText = true;
            HSSFFont font = workbook.CreateFont() as HSSFFont;
            font.IsBold = true;
            headStyle.SetFont(font);

            //内容样式
            HSSFCellStyle cellStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            cellStyle.CloneStyleFrom(headStyle);
            cellStyle.Alignment = HorizontalAlignment.Center;
            font = workbook.CreateFont() as HSSFFont;
            font.IsBold = true;
            cellStyle.SetFont(font);
            HSSFCellStyle cellStyleLeft = workbook.CreateCellStyle() as HSSFCellStyle;
            cellStyleLeft.CloneStyleFrom(cellStyle);
            cellStyleLeft.Alignment = HorizontalAlignment.Left;
            HSSFCellStyle cellStyleRight = workbook.CreateCellStyle() as HSSFCellStyle;
            cellStyleRight.CloneStyleFrom(cellStyle);
            cellStyleRight.Alignment = HorizontalAlignment.Right;

            int rowIndex = 0;
            #region 列头及样式

            for (int i = 0; i < dt_Title.Rows.Count; i++)
            {
                HSSFRow headerRow = sheet.CreateRow(rowIndex) as HSSFRow;
                for (int j = 0; j < dt_Title.Columns.Count; j++)
                {
                    headerRow.CreateCell(dt_Title.Columns[j].Ordinal).SetCellValue(dt_Title.Rows[i][j].ToString());
                    headerRow.GetCell(dt_Title.Columns[j].Ordinal).CellStyle = headStyle;

                }
                rowIndex++;
            }
            for (int i = 0; i < dt_Title.Rows.Count; i++)
            {
                for (int m = 0; m < dt_Title.Columns.Count; m++)
                {
                    int n = m + 1;
                    while (n < dt_Title.Columns.Count)
                    {
                        if (dt_Title.Rows[i][m].ToString() == dt_Title.Rows[i][n].ToString())
                        {
                            n++;
                        }
                        else if (n == dt_Title.Columns.Count)
                        {
                            break;
                        }
                        else
                        {
                            //合并同一行相同的列表头
                            sheet.AddMergedRegion(new CellRangeAddress(i, i, m, n - 1));
                            m = n - 1;
                            break;
                        }
                    }
                    if (n == dt_Title.Columns.Count)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(i, i, m, n - 1));

                    }
                }

            }
            for (int i = 0; i < dt_Title.Columns.Count; i++)
            {
                for (int m = 0; m < dt_Title.Rows.Count; m++)
                {
                    int n = m + 1;
                    while (n < dt_Title.Rows.Count)
                    {
                        if (dt_Title.Rows[m][i].ToString() == dt_Title.Rows[n][i].ToString())
                        {
                            n++;
                        }
                        else if (n == dt_Title.Rows.Count)
                        {
                            break;
                        }
                        else
                        {
                            //合并同一列相同的行表头
                            sheet.AddMergedRegion(new CellRangeAddress(m, n - 1, i, i));
                            m = n - 1;
                            break;
                        }
                    }
                    if (n == dt_Title.Rows.Count)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(m, n - 1, i, i));

                    }
                }
            }




            //sheet.AddMergedRegion(new CellRangeAddress(0, 1, 1, 1));
            //sheet.AddMergedRegion(new CellRangeAddress(0, 1, 2, 2));
            #endregion

            foreach (DataRow row in dtSource.Rows)
            {
                #region 填充内容
                HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
                foreach (DataColumn column in dtSource.Columns)
                {
                    HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
                    newCell.CellStyle = cellStyle;
                    AutoSetType(newCell, column, row);
                }

                #endregion
                rowIndex++;
            }
            AutoSizeColumns(sheet);

            if (ColumnProperty != null)
            {
                string[] ColumnArray = ColumnProperty.Split('|');
                foreach (string Column in ColumnArray)
                {
                    string[] ValueArray = Column.Split(',');
                    if (ValueArray.Length > 2)
                    {
                        int cols = int.Parse(ValueArray[1]);
                        switch (ValueArray[0].ToString())
                        {
                            case "Merge":
                                int precols = 0;
                                int.TryParse(ValueArray[2], out precols);
                                GroupCol(sheet, 1, cols, precols);
                                break;
                            case "Width":
                                int columnWidth = 0;
                                int.TryParse(ValueArray[2], out columnWidth);
                                sheet.SetColumnWidth(cols, columnWidth * 256);
                                break;
                            case "Align":
                                string Value = ValueArray[2].ToString();
                                for (int j = 1; j <= sheet.LastRowNum; j++)
                                {
                                    ICell currentCell = sheet.GetRow(j).GetCell(cols);
                                    switch (Value)
                                    {
                                        case "center":
                                            currentCell.CellStyle = cellStyle;
                                            break;
                                        case "left":
                                            currentCell.CellStyle = cellStyleLeft;
                                            break;
                                        case "right":
                                            currentCell.CellStyle = cellStyleRight;
                                            break;
                                    }
                                }
                                break;
                            case "DataType":
                                string Value2 = ValueArray[2].ToString();
                                for (int j = 1; j <= sheet.LastRowNum; j++)
                                {
                                    ICell currentCell = sheet.GetRow(j).GetCell(cols);
                                    string drValue = currentCell.ToString();
                                    switch (Value2)
                                    {
                                        case "double":
                                            double result;
                                            if (double.TryParse(drValue, out result))
                                                currentCell.SetCellValue(result);
                                            break;
                                        case "string":
                                            currentCell.SetCellValue(drValue.ToString());
                                            break;
                                    }
                                }
                                break;
                        }
                    }
                }
            }
            return workbook;
        }

    }
}
